#include "MonitoringUserSqlite.h"
#define SQNAME "SnaillOTMonitoring.db"
MonitoringUserSqlite::MonitoringUserSqlite(QObject *parent) : QObject(parent)
{
    db = QSqlDatabase::addDatabase("QSQLITE", "myConnection");
    db.setDatabaseName(SQNAME);
    if (!db.open()) {
        qDebug() << "无法打开数据库:" << db.lastError().text();
    } else {
        qDebug() << "数据库已成功打开";
    }
    query = QSqlQuery(db);
    // 确保表存在
    optionsUserSqLite();
    optionsDeviceSqLite();
    optionsDataSqLite();
    optionsWarnRoleSqLite();
    optionsWarnRecodeSqLite();
}
MonitoringUserSqlite& MonitoringUserSqlite::getInstance() {
    static MonitoringUserSqlite instance;
    return instance;
}

MonitoringUserSqlite::~MonitoringUserSqlite()
{
    if (db.isOpen()) {
        db.close();
    }
}



bool MonitoringUserSqlite::createSqLite(const QString &sql)
{

    // 创建一个名为 "users" 的表
    if (!query.exec(sql)) {
        qDebug() << "创建表失败:" << query.lastError().text();
        return false;
    }
    return true;

}

QueryResult MonitoringUserSqlite::insertData(const QString &user, const QString &pass,const QString&email,const QString&role)
{

    if(isUserExists(user)==QUERY_SUCCESS_USER_EXISTS){
        return QUERY_SUCCESS_USER_EXISTS;
    }else {
        const QString insertSql = "INSERT INTO usersmanager (username, password, email, role,created_at,updated_at) VALUES (:username, :password, :email, :role, :created_at ,:updated_at)";
        query.prepare(insertSql);
        query.bindValue(":username", user);
        query.bindValue(":password", pass);
        query.bindValue(":email", email);
        query.bindValue(":role", role);
        query.bindValue(":created_at", MonitoringTool::getNowTimes());
        query.bindValue(":updated_at",  MonitoringTool::getNowTimes());
        db.transaction();
        if (query.exec()) {
            qDebug() << "数据插入成功";
            db.commit();
            // db.close();
            return QUERY_SUCCESS;
        } else {
            qDebug() << "数据插入失败:" << query.lastError().text();
            db.rollback();
            // db.close();
            return QUERY_FAILED;
        }
    }

}

QueryResult MonitoringUserSqlite::isUserExists(const QString &username)
{

    query.prepare("SELECT * FROM usersmanager WHERE username = :username");
    query.bindValue(":username", username);

    if (query.exec()) {
        if (query.next()) {
            // 查询结果集包含数据，说明用户存在
            qDebug() << "用户存在";
            // db.close();
            return QUERY_SUCCESS_USER_EXISTS;
        } else {
            // 查询结果集为空，说明用户不存在
            qDebug() << "用户不存在";
            // db.close();
            return QUERY_SUCCESS_USER_NOT_EXISTS;
        }
    } else {
        // 查询执行失败
        qDebug() << "查询执行失败:" << query.lastError().text();
        // db.close();
        return QUERY_FAILED;
    }
}

QueryResult MonitoringUserSqlite::changePassword(const QString &username,const QString&password)
{

    query.prepare("UPDATE usersmanager SET password = :newPassword, updated_at = :updatedAt WHERE username = :username");
    query.bindValue(":newPassword", password);
    query.bindValue(":username", username);
    query.bindValue(":updatedAt", MonitoringTool::getNowTimes());
    if (query.exec()) {
        if (query.numRowsAffected() > 0) {
            // 更新成功
            // db.close();
            return QUERY_SUCCESS;
        } else {
            // 未找到匹配的用户
            qDebug() << "未找到匹配的用户，密码更新失败";
            // db.close();
            return QUERY_SUCCESS_USER_NOT_EXISTS;
        }
    } else {
        // 更新执行失败
        qDebug() << "更新执行失败:" << query.lastError().text();
        //  db.close();
        return QUERY_FAILED;
    }

}

QueryResult MonitoringUserSqlite::loginGetData(const QString &username, const QString &password)
{

    query.prepare("SELECT id, username, password FROM usersmanager WHERE username = :username AND password = :password");
    query.bindValue(":username", username);
    query.bindValue(":password", password);

    if (query.exec()) {
        if (query.next()) {
            // 查询结果集包含数据，获取用户信息
            //                userInfo.id = query.value(0).toInt();
            //                userInfo.username = query.value(1).toString();
            //                userInfo.password = query.value(2).toString();
            //                qDebug() << "用户信息查询成功:" << userInfo.id << userInfo.username << userInfo.password;
            // db.close();
            return QUERY_SUCCESS;
        } else {
            // 查询结果集为空，说明未找到匹配的用户
            qDebug() << "未找到匹配的用户";
            // db.close();
            return QUERY_SUCCESS_USER_NOT_EXISTS;
        }
    } else {
        // 查询执行失败
        qDebug() << "查询执行失败:" << query.lastError().text();
        // db.close();
        return QUERY_FAILED;
    }
}

QueryResult MonitoringUserSqlite::changeEmail(const QString &username, const QString &email)
{

    query.prepare("UPDATE usersmanager SET email = :newemail, updated_at = :updatedAt WHERE username = :username");
    query.bindValue(":newemail", email);
    query.bindValue(":username", username);
    query.bindValue(":updatedAt", MonitoringTool::getNowTimes());
    if (query.exec()) {
        if (query.numRowsAffected() > 0) {
            // 更新成功
            // db.close();
            return QUERY_SUCCESS;
        } else {
            // 未找到匹配的用户
            qDebug() << "未找到匹配的用户，密码更新失败";
            // db.close();
            return QUERY_SUCCESS_USER_NOT_EXISTS;
        }
    } else {
        // 更新执行失败
        qDebug() << "更新执行失败:" << query.lastError().text();
        //  db.close();
        return QUERY_FAILED;
    }
}


bool MonitoringUserSqlite::optionsUserSqLite()
{


    const QString createTableSql = "CREATE TABLE IF NOT EXISTS usersmanager ("
                                   "id INTEGER PRIMARY KEY AUTOINCREMENT, "
                                   "username TEXT NOT NULL, "
                                   "password TEXT NOT NULL, "
                                   "email TEXT, "
                                   "role TEXT NOT NULL,"
                                   "created_at TEXT, "
                                   "updated_at TEXT"
                                   ")";
    return createSqLite(createTableSql);
}

bool MonitoringUserSqlite::optionsDeviceSqLite()
{

    const QString createTableSql = "CREATE TABLE IF NOT EXISTS devicemanager ("
                                   "id INTEGER PRIMARY KEY AUTOINCREMENT, "
                                   "user_id TEXT NOT NULL, "
                                   "name TEXT NOT NULL, "
                                   "type TEXT NOT NULL, "
                                   "location TEXT, "
                                   "manufacturer TEXT NOT NULL,"
                                   "model TEXT, "
                                   "status TEXT, "
                                   "installation_date TEXT"
                                   ")";
    return createSqLite(createTableSql);
}

bool MonitoringUserSqlite::optionsDataSqLite()
{
    const QString createTableSql = "CREATE TABLE IF NOT EXISTS datamanager ("
                                   "id INTEGER PRIMARY KEY AUTOINCREMENT, "
                                   "user_id TEXT NOT NULL, "
                                   "device_id TEXT NOT NULL, "
                                   "timestamp TEXT NOT NULL, "
                                   "temperature TEXT NOT NULL, "
                                   "humidity TEXT, "
                                   "light TEXT NOT NULL,"
                                   "co2 TEXT, "
                                   "pressure TEXT"
                                   ")";
    return createSqLite(createTableSql);
}

bool MonitoringUserSqlite::optionsWarnRoleSqLite()
{
    const QString createTableSql = "CREATE TABLE IF NOT EXISTS warnrolemanager ("
                                   "id INTEGER PRIMARY KEY AUTOINCREMENT, "
                                   "device_id  TEXT NOT NULL UNIQUE, "
                                   "description TEXT NOT NULL, "
                                   "maxHemperature TEXT NOT NULL, "
                                   "minHemperature TEXT, "
                                   "maxTemperature TEXT NOT NULL,"
                                   "minTemperature TEXT, "
                                   "action TEXT"
                                   ")";
    return createSqLite(createTableSql);
}

bool MonitoringUserSqlite::optionsWarnRecodeSqLite()
{
    const QString createTableSql = "CREATE TABLE IF NOT EXISTS warnrecodemanager ("
                                   "id INTEGER PRIMARY KEY AUTOINCREMENT, "
                                   "device_id TEXT NOT NULL, "
                                   "timestamp TEXT NOT NULL, "
                                   "content TEXT NOT NULL, "
                                   "status TEXT, "
                                   "note TEXT"
                                   ")";
    return createSqLite(createTableSql);
}
QueryResult MonitoringUserSqlite::insertDeviceData(const QString&user_id,const QString &name, const QString &type, const QString &location, const QString &manufacturer, const QString &model, const QString &status)
{

    const QString insertSql = "INSERT INTO devicemanager (user_id,name, type, location, manufacturer,model,status,installation_date) VALUES (:user_id,:name, :type, :location, :manufacturer, :model ,:status,:installation_date)";
    query.prepare(insertSql);
    query.bindValue(":user_id", user_id);
    query.bindValue(":name", name);
    query.bindValue(":type", type);
    query.bindValue(":location", location);
    query.bindValue(":manufacturer", manufacturer);
    query.bindValue(":status", status);
    query.bindValue(":model", model);
    query.bindValue(":installation_date",  MonitoringTool::getNowTimes());
    db.transaction();
    if (query.exec()) {
        qDebug() << "数据插入成功";
        db.commit();
        // db.close();
        return QUERY_SUCCESS;
    } else {
        qDebug() << "数据插入失败:" << query.lastError().text();
        db.rollback();
        // db.close();
        return QUERY_FAILED;
    }
}
userModel* MonitoringUserSqlite::getSqLite(const QString &username)
{


    query.prepare("SELECT id, username, password, email, role,created_at ,updated_at  FROM usersmanager WHERE username = :username");
    query.bindValue(":username", username);
    qDebug()<<"ffff"<<username;
    if (query.exec()) {
        while (query.next()) {
            int id = query.value(0).toInt();
            QString name = query.value(1).toString();
            QString password = query.value(2).toString();
            QString email = query.value(3).toString();
            QString role = query.value(4).toString();
            QString created_at = query.value(5).toString();
            QString updated_at = query.value(6).toString();
            qDebug() << "ID:" << id << "Name:" << name << "Age:" << password<< "email:" << email<< "role:" << role;
            userModel*model=new userModel(id,name,email,role,created_at,updated_at,password);
            return model;
        }
        // db.close();
        qDebug()<<"==="<<query.next();
        return nullptr;
    } else {
        qDebug() << "查询数据失败:" << query.lastError().text();
        return nullptr;
    }

}

QList<deviceModel> MonitoringUserSqlite::checkList()
{
    QList<deviceModel>deviceList;
    QString querySql = "SELECT * FROM devicemanager";
    query.prepare(querySql);
    if (query.exec()) {
        while (query.next()) {
            deviceModel device;
            device.id = query.value("id").toString();
            device.user_id = query.value("user_id").toString();
            device.name = query.value("name").toString();
            device.type = query.value("type").toString();
            device.location = query.value("location").toString();
            device.manufacturer = query.value("manufacturer").toString();
            device.model = query.value("model").toString();
            device.status = query.value("status").toString();
            device.installation_date = query.value("installation_date").toString();
            deviceList.append(device);
        }

    } else {
        qDebug() << "查询执行失败:" << query.lastError().text();
        return deviceList;
    }
    return deviceList;

}

QueryResult MonitoringUserSqlite::deviceDelete(const QString &id)
{
    // 准备SQL删除语句
    query.prepare("DELETE FROM devicemanager WHERE id = :id");
    // 绑定设备ID到SQL语句中的占位符
    query.bindValue(":id",id);

    if (query.exec()) {
        return QUERY_SUCCESS;
    } else {
        return QUERY_FAILED;
    }
}

QueryResult MonitoringUserSqlite::deviceUpdate(const QString &str, const QString &id, const QString &userId,const QString &col)
{
    switch (col.toInt()) {
    case 2: query.prepare("UPDATE devicemanager SET name = :str WHERE id = :id AND user_id = :userId"); break;
    case 3: query.prepare("UPDATE devicemanager SET type = :str WHERE id = :id AND user_id = :userId"); break;
    case 4: query.prepare("UPDATE devicemanager SET location = :str WHERE id = :id AND user_id = :userId"); break;
    case 5: query.prepare("UPDATE devicemanager SET manufacturer = :str WHERE id = :id AND user_id = :userId"); break;
    case 6: query.prepare("UPDATE devicemanager SET model = :str WHERE id = :id AND user_id = :userId"); break;
    case 7: query.prepare("UPDATE devicemanager SET status = :str WHERE id = :id AND user_id = :userId"); break;
    default: break;
    }


    query.bindValue(":str", str);
    query.bindValue(":id", id);
    query.bindValue(":userId", userId);

    if (query.exec()) {
        qDebug() << "数据更新成功";
        return QUERY_SUCCESS;
    } else {
        qDebug() << "数据更新失败，错误信息: " << query.lastError().text();
        return QUERY_FAILED;
    }
}

QueryResult MonitoringUserSqlite::insertDataData(const QString &user_id, const QString &device_id, const QString &temperature, const QString &humidity, const QString &light, const QString &co2,const QString &pressure)
{
    const QString insertSql = "INSERT INTO datamanager (user_id,device_id,timestamp,temperature, humidity, light,co2,pressure) VALUES (:user_id,:device_id,:timestamp, :temperature, :humidity, :light ,:co2,:pressure)";
    query.prepare(insertSql);
    query.bindValue(":user_id", user_id);
    query.bindValue(":device_id", device_id);
    query.bindValue(":timestamp", MonitoringTool::getNowTimes());
    query.bindValue(":temperature", temperature);
    query.bindValue(":humidity", humidity);
    query.bindValue(":light", light);
    query.bindValue(":co2", co2);
    query.bindValue(":pressure", pressure);
    db.transaction();
    if (query.exec()) {
        qDebug() << "数据插入成功";
        db.commit();
        // db.close();
        return QUERY_SUCCESS;
    } else {
        qDebug() << "数据插入失败:" << query.lastError().text();
        db.rollback();
        // db.close();
        return QUERY_FAILED;
    }
}

QList<dataModel> MonitoringUserSqlite::selectAllDataData()
{
    QList<dataModel>dataList;
    QString querySql = "SELECT * FROM datamanager";
    query.prepare(querySql);
    if (query.exec()) {
        while (query.next()) {
            dataModel data;
            data.id = query.value("id").toString();
            data.user_id = query.value("user_id").toString();
            data.device_id = query.value("device_id").toString();
            data.timestamp = query.value("timestamp").toString();
            data.temperature = query.value("temperature").toString();
            data.humidity = query.value("humidity").toString();
            data.light = query.value("light").toString();
            data.co2 = query.value("co2").toString();
            data.pressure = query.value("pressure").toString();
            dataList.append(data);
        }
    } else {
        qDebug() << "查询执行失败:" << query.lastError().text();
        return dataList;
    }
    return dataList;
}

QList<dataModel> MonitoringUserSqlite::selectDataToReasonData(const QString &startTime, const QString &endTime, const QString &device_id)
{
    QList<dataModel>dataList;
    QString queryStr = "SELECT * FROM datamanager";
    QString whereClause;
    if (!startTime.isEmpty() && !endTime.isEmpty() && !device_id.isEmpty()) {
               whereClause += QString(" WHERE timestamp BETWEEN '%1' AND '%2' AND device_id = '%3'")
                              .arg(startTime).arg(endTime).arg(device_id);
           } else if (!startTime.isEmpty() && !endTime.isEmpty() && device_id.isEmpty()) {
               whereClause += QString(" WHERE timestamp BETWEEN '%1' AND '%2'")
                              .arg(startTime).arg(endTime);
           } else if (!startTime.isEmpty() && endTime.isEmpty() && !device_id.isEmpty()) {
               whereClause += QString(" WHERE timestamp >= '%1' AND device_id = '%2'")
                              .arg(startTime).arg(device_id);
           } else if (!startTime.isEmpty() && endTime.isEmpty() && device_id.isEmpty()) {
               whereClause += QString(" WHERE timestamp >= '%1'").arg(startTime);
           } else if (startTime.isEmpty() && !endTime.isEmpty() && !device_id.isEmpty()) {
               whereClause += QString(" WHERE timestamp <= '%1' AND device_id = '%2'")
                              .arg(endTime).arg(device_id);
           } else if (startTime.isEmpty() && !endTime.isEmpty() && device_id.isEmpty()) {
               whereClause += QString(" WHERE timestamp <= '%1'").arg(endTime);
           } else if (startTime.isEmpty() && endTime.isEmpty() && !device_id.isEmpty()) {
               whereClause += QString(" WHERE device_id = '%1'").arg(device_id);
           }

    queryStr += whereClause;
    qDebug()<<"queryStr==="<<queryStr;
    if (query.exec(queryStr)) {
        while (query.next()) {
            dataModel data;
            data.id = query.value("id").toString();
            data.user_id = query.value("user_id").toString();
            data.device_id = query.value("device_id").toString();
            data.timestamp = query.value("timestamp").toString();
            data.temperature = query.value("temperature").toString();
            data.humidity = query.value("humidity").toString();
            data.light = query.value("light").toString();
            data.co2 = query.value("co2").toString();
            data.pressure = query.value("pressure").toString();
            dataList.append(data);
        }
    } else {
        qDebug() << "查询执行失败:" << query.lastError().text();
        return dataList;
    }
    return dataList;


}

QueryResult MonitoringUserSqlite::insertWarnRoleData(const QString &device_id, const QString &description, const QString &maxHemperature, const QString &minHemperature, const QString &maxTemperature, const QString &minTemperature, const QString &action)
{
            const QString insertSql = "INSERT OR REPLACE INTO warnrolemanager (device_id,description,maxHemperature, minHemperature, maxTemperature,minTemperature,action) VALUES (:device_id,:description, :maxHemperature, :minHemperature, :maxTemperature ,:minTemperature,:action)";
            QSqlQuery query(db);
            query.prepare(insertSql);
            query.bindValue(":device_id", device_id);
            query.bindValue(":description", description);
            query.bindValue(":maxHemperature", maxHemperature);
            query.bindValue(":minHemperature", minHemperature);
            query.bindValue(":maxTemperature", maxTemperature);
            query.bindValue(":minTemperature", minTemperature);
            query.bindValue(":action", action);

            db.transaction();
            if (query.exec()) {
                qDebug() << "数据插入或更新成功";
                db.commit();
                return QUERY_SUCCESS;
            } else {
                qDebug() << "数据插入或更新失败:" << query.lastError().text();
                db.rollback();
                return QUERY_FAILED;
            }
}

warnRoleModel *MonitoringUserSqlite::getWarnRoleSqLite(const QString &device_id)
{
    query.prepare("SELECT id,device_id,description,maxHemperature, minHemperature, maxTemperature,minTemperature,action  FROM warnrolemanager WHERE device_id = :device_id");
    query.bindValue(":device_id", device_id);
    if (query.exec()) {
        while (query.next()) {
            QString id = query.value(0).toString();
            QString device_idd = query.value(1).toString();
            QString description = query.value(2).toString();
            QString maxHemperature = query.value(3).toString();
            QString minHemperature = query.value(4).toString();
            QString maxTemperature = query.value(5).toString();
            QString minTemperature = query.value(6).toString();
            QString action = query.value(7).toString();

            warnRoleModel*model=new warnRoleModel(id,device_idd,description,maxHemperature,minHemperature,maxTemperature,minTemperature,action);
            return model;
        }
        // db.close();
      //  qDebug()<<"==="<<query.next();
        return nullptr;
    } else {
        qDebug() << "查询数据失败:" << query.lastError().text();
        return nullptr;
    }

}

QueryResult MonitoringUserSqlite::insertWarnRecodeData(const QString &device_id, const QString &content, const QString &status, const QString &note)
{
    const QString insertSql = "INSERT INTO warnrecodemanager (device_id,timestamp, content, status, note) VALUES (:device_id,:timestamp, :content, :status, :note)";
    query.prepare(insertSql);
    query.bindValue(":device_id", device_id);
    query.bindValue(":timestamp", MonitoringTool::getNowTimes());
    query.bindValue(":content", content);
    query.bindValue(":status", status);
    query.bindValue(":note", note);
    db.transaction();
    if (query.exec()) {
        qDebug() << "数据插入成功";
        db.commit();
        // db.close();
        return QUERY_SUCCESS;
    } else {
        qDebug() << "数据插入失败:" << query.lastError().text();
        db.rollback();
        // db.close();
        return QUERY_FAILED;
    }
}

QList<warnRecode> MonitoringUserSqlite::getWarnRecodeData()
{
    QList<warnRecode>dataList;
    QString queryStr = "SELECT * FROM warnrecodemanager";
    if (query.exec(queryStr)) {
        while (query.next()) {
            warnRecode data;
            data.id = query.value("id").toString();
            data.device_id = query.value("device_id").toString();
            data.timestamp = query.value("timestamp").toString();
            data.content = query.value("content").toString();
            data.status = query.value("status").toString();
            data.note = query.value("note").toString();
            dataList.append(data);
        }
    } else {
        qDebug() << "查询执行失败:" << query.lastError().text();
        return dataList;
    }
    return dataList;

}

QList<deviceModel> MonitoringUserSqlite::getDeviceGroupListSqLite(const QString &type)
{
    QList<deviceModel>dataList;
    query.prepare("SELECT id,user_id,name,type, location, manufacturer,model,status,installation_date  FROM devicemanager WHERE type = :type");
    query.bindValue(":type", type);
    if (query.exec()) {
        while (query.next()) {
            deviceModel dmodel;
             dmodel.id = query.value(0).toString();
            dmodel.user_id = query.value(1).toString();
            dmodel.name = query.value(2).toString();
            dmodel.type = query.value(3).toString();
            dmodel.location = query.value(4).toString();
            dmodel.manufacturer = query.value(5).toString();
            dmodel.model = query.value(6).toString();
            dmodel.status = query.value(7).toString();
             dmodel.installation_date = query.value(8).toString();

            dataList.append(dmodel);
        }
        // db.close();
      //  qDebug()<<"==="<<query.next();
        return dataList;
    } else {
        qDebug() << "查询数据失败:" << query.lastError().text();
        return dataList;
    }
}
